﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


using System.Data;
using System.Data.Common;
using Husb.Data;
using Husb.DataUtil;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Caching;

using CentralOffice.Task.Data;

using ApprovalTable = CentralOffice.Task.Data.ApprovalDataSet.ApprovalsDataTable;
using ApprovalRow = CentralOffice.Task.Data.ApprovalDataSet.ApprovalsRow;

namespace CentralOffice.Task.DataAccess
{
    public class ApprovalAdapter : DataAccessAdapter<CentralOffice.Task.Data.ApprovalDataSet, ApprovalTable, ApprovalRow>
    {
        private const string SelectFirstByApplicationId = "SELECT TOP 1 Id, ApplicationId, StepId, ApprovedBy, DateApproved, ContentText, Opinion, ResultApproved, PreviousStep, IsFinal, IsDeleted, IsActive, CreatedTime, CreatedBy, ModifiedTime, LastModifiedBy, Version, [Description] FROM Tasks.Approvals WHERE ApplicationId = @ApplicationId ORDER BY CreatedTime ";

        private const string SelectRejectionByApplicationId = "SELECT Id, ApplicationId, StepId, ApprovedBy, DateApproved, ContentText, Opinion, ResultApproved, PreviousStep, IsFinal, IsDeleted, IsActive, CreatedTime, CreatedBy, ModifiedTime, LastModifiedBy, Version, [Description] FROM Tasks.Approvals WHERE ApplicationId = @ApplicationId AND ResultApproved = 0 ORDER BY CreatedTime ";

        //private const string SelectByPreviousStepId = "SELECT Id, ApplicationId, StepId, ApprovedBy, DateApproved, ContentText, Opinion, ResultApproved, PreviousStep, IsFinal, IsDeleted, IsActive, CreatedTime, CreatedBy, ModifiedTime, LastModifiedBy, Version, [Description] FROM Tasks.Approvals WHERE PreviousStep = @PreviousStep ";
        private const string SelectByPreviousStepId = "SELECT     Id, ApplicationId, ApprovedBy, DateApproved, ContentText, Opinion, ResultApproved, PreviousStep, IsFinal, ApprovalAttachments, IsDeleted, IsActive,  CreatedTime, CreatedBy, ModifiedTime, LastModifiedBy, Version, Description, WarningDate, Name, ApplicationCode, Category, UrgencyDegree,  DateOfApplication, Applicant, ApprovalContent, Status, Attachments, ApplicationIsDeleted, PreviousApprovedBy, PreviousDateApproved,    PreviousContentText, PreviousOpinion, PreviousResultApproved, ApplicationExecutor, IsApproved, SequenceNumber, NextStepIsApproved,    ElapsedTime, StepId  FROM Tasks.View_Approvals WHERE PreviousStep = @PreviousStep ";


        private const string DeleteByPreviousStep = "DELETE FROM [Tasks].[Approvals]  WHERE PreviousStep = @PreviousStep ";

        //private const string SQL_DeleteByApplicationId = "UPDATE [Tasks].[Approvals]  SET IsDeleted = 1 WHERE ApplicationId = @ApplicationId ";
        private const string SQL_DeleteByApplicationId = "DELETE FROM  [Tasks].[Approvals] WHERE ApplicationId = @ApplicationId ";


        private const string SelectTimeoutApproval = @"SELECT ApprovedBy, COUNT( ApprovedBy ) AS TimeoutCount, SUM(ElapsedTime) AS ElapsedTime
FROM (
SELECT     ApprovedBy, DATEDIFF(minute, WarningDate, CASE WHEN DateApproved IS NULL THEN GETDATE() ELSE DateApproved END) AS ElapsedTime
FROM         Tasks.Approvals
WHERE IsDeleted = 0 AND IsActive = 1 AND CreatedTime >= '{0}' AND CreatedTime < '{1}'
) as tmp
WHERE ElapsedTime > 0
GROUP BY ApprovedBy";

        private const string SelectNormalApproval = @"SELECT ApprovedBy, COUNT( ApprovedBy ) AS NormalCount
FROM (
SELECT     ApprovedBy, DATEDIFF(minute, WarningDate, CASE WHEN DateApproved IS NULL THEN GETDATE() ELSE DateApproved END) AS ElapsedTime
FROM         Tasks.Approvals 
WHERE IsDeleted = 0 AND IsActive = 1 AND CreatedTime >= '{0}' AND CreatedTime < '{1}'
) as tmp
WHERE ElapsedTime <= 0
GROUP BY ApprovedBy";//, SUM(ElapsedTime) AS ElapsedTime


        public ApprovalAdapter()
        {
            this.TableName = "Approvals";

            GetAllStoredProc = "Tasks.Approvals_SelectApprovalsAll";
            InsertStoredProc = "Tasks.Approvals_InsertApproval";
            UpdateStoredProc = "Tasks.Approvals_UpdateApproval";
            DeleteStoredProc = "Tasks.Approvals_DeleteApproval";

            GetByIdStoredProc = "Tasks.Approvals_SelectApproval";
            GetByNameStoredProc = "";
            GetPagedStoredProc = "Tasks.Approvals_SelectApprovalsPaged";
            GetRowCountStoredProc = "Tasks.Approvals_SelectApprovalsRowCount";
            FindStoredProc = "Tasks.Approvals_SelectApprovalsDynamic";

            GetByMasterIdStoredProc = "Tasks.Approvals_SelectApprovalsByApplicationId";
            MasterFieldName = "ApplicationId";

            //LogicalDeleteStoredProc = "Tasks.Tasks_DeleteTaskLogical";
        }
        protected override void PopulateInsertParameters(Database db, DbCommand cmd)
        {
            db.AddInParameter(cmd, "Id", DbType.Guid, "Id", DataRowVersion.Current);
            db.AddInParameter(cmd, "ApplicationId", DbType.Guid, "ApplicationId", DataRowVersion.Current);

            db.AddInParameter(cmd, "StepId", DbType.Guid, "StepId", DataRowVersion.Current);

            db.AddInParameter(cmd, "ApprovedBy", DbType.AnsiString, "ApprovedBy", DataRowVersion.Current);
            db.AddInParameter(cmd, "DateApproved", DbType.DateTime, "DateApproved", DataRowVersion.Current);
            db.AddInParameter(cmd, "ContentText", DbType.String, "ContentText", DataRowVersion.Current);
            db.AddInParameter(cmd, "Opinion", DbType.AnsiString, "Opinion", DataRowVersion.Current);
            db.AddInParameter(cmd, "ResultApproved", DbType.Boolean, "ResultApproved", DataRowVersion.Current);
            db.AddInParameter(cmd, "PreviousStep", DbType.Guid, "PreviousStep", DataRowVersion.Current);
            db.AddInParameter(cmd, "IsFinal", DbType.Boolean, "IsFinal", DataRowVersion.Current);
            db.AddInParameter(cmd, "Attachments", DbType.String, "ApprovalAttachments", DataRowVersion.Current);
            db.AddInParameter(cmd, "NextStepIsApproved", DbType.Boolean, "NextStepIsApproved", DataRowVersion.Current);
            db.AddInParameter(cmd, "IsApproved", DbType.Boolean, "IsApproved", DataRowVersion.Current);
            db.AddInParameter(cmd, "SequenceNumber", DbType.Int32, "SequenceNumber", DataRowVersion.Current);

            db.AddInParameter(cmd, "WarningDate", DbType.DateTime, "WarningDate", DataRowVersion.Current);

            db.AddInParameter(cmd, "IsDeleted", DbType.Boolean, "IsDeleted", DataRowVersion.Current);
            db.AddInParameter(cmd, "IsActive", DbType.Boolean, "IsActive", DataRowVersion.Current);
            db.AddInParameter(cmd, "CreatedTime", DbType.DateTime, "CreatedTime", DataRowVersion.Current);
            db.AddInParameter(cmd, "CreatedBy", DbType.Guid, "CreatedBy", DataRowVersion.Current);
            db.AddInParameter(cmd, "ModifiedTime", DbType.DateTime, "ModifiedTime", DataRowVersion.Current);
            db.AddInParameter(cmd, "LastModifiedBy", DbType.Guid, "LastModifiedBy", DataRowVersion.Current);
            db.AddInParameter(cmd, "Version", DbType.Int32, "Version", DataRowVersion.Current);
            db.AddInParameter(cmd, "Description", DbType.AnsiString, "Description", DataRowVersion.Current);
        }

        public ApprovalRow GetFirstStep(Guid applicationId)
        {
            List<DatabaseParameter> parameters = new List<DatabaseParameter>();
            parameters.Add(new DatabaseParameter("ApplicationId", DbType.Guid, applicationId));
            ApprovalDataSet ds = Husb.DataUtil.DataAccessUtil.GetDataSet<ApprovalDataSet>(SelectFirstByApplicationId, parameters, this.TableName, false, false, null);

            ApprovalTable dt = ds.Approvals;
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0] as ApprovalRow;
            }

            return null;
        }

        public ApprovalRow GetRejectedStep(Guid applicationId)
        {
            List<DatabaseParameter> parameters = new List<DatabaseParameter>();
            parameters.Add(new DatabaseParameter("ApplicationId", DbType.Guid, applicationId));
            ApprovalDataSet ds = Husb.DataUtil.DataAccessUtil.GetDataSet<ApprovalDataSet>(SelectRejectionByApplicationId, parameters, this.TableName, false, false, null);

            ApprovalTable dt = ds.Approvals;
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0] as ApprovalRow;
            }

            return null;
        }

        // GetPreviousStepId

        public ApprovalRow GetByPreviousStepId(Guid approvalId)
        {
            List<DatabaseParameter> parameters = new List<DatabaseParameter>();
            parameters.Add(new DatabaseParameter("PreviousStep", DbType.Guid, approvalId));
            ApprovalDataSet ds = Husb.DataUtil.DataAccessUtil.GetDataSet<ApprovalDataSet>(SelectByPreviousStepId, parameters, this.TableName, false, false, null);

            ApprovalTable dt = ds.Approvals;
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0] as ApprovalRow;
            }

            return null;
        }

        public int DeleteByPreviousStepId(Guid approvalId)
        {
            List<DatabaseParameter> parameters = new List<DatabaseParameter>();
            parameters.Add(new DatabaseParameter("PreviousStep", DbType.Guid, approvalId));
            int cnt = Husb.DataUtil.DataAccessUtil.ExecuteNonQuery(DeleteByPreviousStep, parameters, false, false, null);
            
            return cnt;
        }

        public int DeleteByApplicationId(Guid applicationId)
        {
            List<DatabaseParameter> parameters = new List<DatabaseParameter>();
            parameters.Add(new DatabaseParameter("ApplicationId", DbType.Guid, applicationId));
            int cnt = Husb.DataUtil.DataAccessUtil.ExecuteNonQuery(SQL_DeleteByApplicationId, parameters, false, false,  null);

            return cnt;
        }

        public DataTable GetApprovalTimeSpan(  decimal deduct, DateTime start, DateTime end  ) //int delay,
        {
            DataTable dt = new DataTable("ApprovalTimeSpan");    
            dt.Columns.Add("ApprovedBy", typeof(System.String));  // 审批人
            dt.Columns.Add("NormalCount", typeof(System.Int32));  // 按时审批次数
            dt.Columns.Add("TimeoutCount", typeof(System.Int32)); // 超时审批次数
            dt.Columns.Add("ElapsedTime", typeof(System.Int32));  // 超时分钟数
            dt.Columns.Add("Deduct", typeof(System.Decimal));     // 每分钟扣款
            dt.Columns.Add("Sum", typeof(System.Decimal), " ElapsedTime * Deduct ");

            // 得到按时审批的数据
            DataTable normal = Husb.DataUtil.DataAccessUtil.GetDataTable(String.Format(SelectNormalApproval,
                start.ToString("yyyy-MM-dd")
                ,end.AddDays(1).ToString("yyyy-MM-dd")
                //,delay.ToString()
                ));
            // 将按时审批信息导入到统计表中
            foreach (DataRow row in normal.Rows)
            {
                DataRow r = dt.NewRow();
                r["ApprovedBy"] = row["ApprovedBy"];
                //r["ApprovedBy"] = row["ApprovedBy"];
                r["NormalCount"] = row["NormalCount"];
                //r["ElapsedTime"] = row["ElapsedTime"];
                //r["Deduct"] = deduct;

                dt.Rows.Add(r);
            }
            // 得到超时审批的数据
            DataTable timeout = Husb.DataUtil.DataAccessUtil.GetDataTable(String.Format(SelectTimeoutApproval
                ,start.ToString("yyyy-MM-dd")
                ,end.AddDays(1).ToString("yyyy-MM-dd")
                //,delay.ToString()
                )                );
            // 如果没有按时审批信息，就直接导入超时审批信息
            if (dt.Rows.Count == 0)
            {
                foreach (DataRow row in timeout.Rows)
                {
                    DataRow r = dt.NewRow();
                    r["ApprovedBy"] = row["ApprovedBy"];
                    //r["ApprovedBy"] = row["ApprovedBy"];
                    r["TimeoutCount"] = row["TimeoutCount"];
                    r["ElapsedTime"] = row["ElapsedTime"];
                    r["Deduct"] = deduct;

                    dt.Rows.Add(r);
                }
            }
            else
            {
                // 有按时审批信息，先合并此人的超时审批
                foreach (DataRow row in timeout.Rows)
                {
                    // 首先将相同审批者的信息合并
                    int cnt = dt.Rows.Count;
                    int p = 0;
                    while (p < cnt)
                    {
                        // 合并此人的超时审批
                        if (dt.Rows[p]["ApprovedBy"].ToString() == row["ApprovedBy"].ToString())
                        {
                            dt.Rows[p]["TimeoutCount"] = row["TimeoutCount"];
                            dt.Rows[p]["ElapsedTime"] = row["ElapsedTime"];
                            dt.Rows[p]["Deduct"] = deduct;

                            break;
                        }
                        else
                        {
                            p++;
                        }
                    }
                    // 没有找到审批者，添加一行
                    if (p == cnt)
                    {
                        DataRow r = dt.NewRow();
                        r["ApprovedBy"] = row["ApprovedBy"];
                        //r["ApprovedBy"] = row["ApprovedBy"];
                        r["NormalCount"] = 0;
                        r["TimeoutCount"] = row["TimeoutCount"];
                        r["ElapsedTime"] = row["ElapsedTime"];
                        r["Deduct"] = deduct;

                        // 加上这句
                        dt.Rows.Add(r);
                    }
                }
            }
            return dt;
        }
        
    }
}
